USE [JaxDug]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetPresentationById') AND TYPE IN (N'P', N'PC'))
BEGIN
	DROP PROC GetPresentationById
	PRINT 'DROP PROC GetPresentationById'
END
GO

-- ===============================================================
-- Author:		RbH
-- Create date: 2012.03.21
-- Description:	Selects detail info about a Presentation
-- Ussage:		EXEC GetPresentationById 1
-- ===============================================================
CREATE PROCEDURE dbo.GetPresentationById
	@PresentationID INT
AS
BEGIN
	SET NOCOUNT ON;
		
    SELECT
		p.PresentationID, p.Title, p.Synopsis, p.StartDateTime, p.EndDateTime,
		l.[Description] AS 'LocationDescription', l.StreetAddress1, l.City, l.State, l.Zip,
		u.UserID AS 'SpeakerID', u.FirstName, u.LastName, u.Bio
	FROM dbo.Presentations p
		INNER JOIN dbo.Users u ON u.UserID = p.SpeakerID
		INNER JOIN dbo.Locations l ON p.LocationID = l.LocationID
	WHERE p.PresentationID = @PresentationID
	ORDER BY p.PresentationID ASC
	
	SELECT DISTINCT t.TagID, t.Tag
	FROM dbo.Tags t
		INNER JOIN dbo.PresentationTags pt ON t.TagID = pt.TagID
	WHERE pt.PresentationID = @PresentationID
END
GO

PRINT 'CREATE PROC GetPresentationById'

GRANT EXEC ON GetPresentationById TO PublicDbUser
GO

PRINT 'GRANT EXEC ON GetPresentationById TO PublicDbUser'